package com.augurit.gzcgwsp.excelimportexport.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.gzcgwsp.common.exp.PoiExcelSheetCopy;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.commonex.util.lang.StringUtilsEX;
import com.augurit.gzcgwsp.excelimportexport.dao.YwRqQyjyxkRqssylbDao;
import com.augurit.gzcgwsp.excelimportexport.entity.YwRqQyjyxkRqssylb;
import com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssylbService;
import com.augurit.gzcgwsp.excelimportexport.util.GetFileName;
import com.augurit.gzcgwsp.rqqyjyxk.entity.YwRqQyjyxkFinalRqssylb;
import com.augurit.gzcgwsp.rqqyjyxk.service.IYwRqQyjyxkFinalRqssylbService;
import com.augurit.gzcgwsp.ssylb.dao.YwRqRqssylbDao;
import com.augurit.gzcgwsp.ssylb.entity.YwRqRqssylb;
import com.augurit.gzcgwsp.webservice.SFTPUtil;

/**
 * 广州市燃气经营许可证许可审批------五、1 燃气设施一览表
 * @author xgw
 *
 */
@Service
@Transactional
public class YwRqQyjyxkRqssylbServiceImpl implements IYwRqQyjyxkRqssylbService {
    private final static Logger log=Logger.getLogger(YwRqQyjyxkRqssylbServiceImpl.class);
    
	@Autowired
	private YwRqQyjyxkRqssylbDao ywRqQyjyxkRqssylbDao;
	@Autowired
	private YwRqRqssylbDao ywRqRqssylbDao;
	@Autowired
	private IYwRqQyjyxkFinalRqssylbService rqssylbService;
	
	/**
	 * 保存 燃气设施一览表
	 * @param ywRqQyjyxkRqssylb
	 */
	public void save(YwRqQyjyxkRqssylb ywRqQyjyxkRqssylb) {
		ywRqQyjyxkRqssylbDao.save(ywRqQyjyxkRqssylb);
	}
	
	/**
	 * 解析 燃气设施一览表
	 * @param hssfsheet
	 * @param acceptNo
	 * @param qyid
	 * @return
	 */
	public YwRqQyjyxkRqssylb parseRqqxsbylb(HSSFSheet hssfsheet,long rqjyxkid,String acceptNo, Long qyid, String username) {
		String xh = null;//序号
		String mc = null;//名称
		String dz = null;//地址
		String sslb = null;//设施类别
			String hql = "from YwRqQyjyxkRqssylb t where t.qyid = "+qyid+"";
			List<YwRqQyjyxkRqssylb> list=ywRqQyjyxkRqssylbDao.find(hql);
			for(int i=0;i<list.size();i++)
			{
				ywRqQyjyxkRqssylbDao.delete(list.get(i));
			}
			
			String hql2 = "from YwRqRqssylb t where t.qyjyxkid = "+rqjyxkid+"";
			List<YwRqRqssylb> list2=ywRqRqssylbDao.find(hql2);
			for(int i=0;i<list2.size();i++)
			{
				ywRqRqssylbDao.delete(list2.get(i));
			}
		
		try {
			HSSFRow hssfrow = hssfsheet.getRow(0);// 第一行
			// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
			for (int j = 15; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
				hssfrow = hssfsheet.getRow(j);
				// 判断是否还存在需要导入的数据
				if (hssfrow == null) {
					// System.out.println("这里已没有数据，在第" + i + "列,第" + j + "行");
					break;
				}				
				if(j >= 15){
					if (hssfrow.getCell((short) 0) == null) {
						xh = "";
					} else if (hssfrow.getCell((short) 0).getCellType() == 0) {
						xh = new Double(hssfrow.getCell((short) 0)
								.getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						xh = hssfrow.getCell((short) 0).getStringCellValue()
								.trim();
					}
					if (hssfrow.getCell((short) 1) == null) {
						mc = "";
					} else if (hssfrow.getCell((short) 1).getCellType() == 0) {
						mc = new Double(hssfrow.getCell((short) 1)
								.getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						mc = hssfrow.getCell((short) 1).getStringCellValue()
								.trim();
					}
					if (hssfrow.getCell((short) 2) == null) {
						dz = "";
					} else if (hssfrow.getCell((short) 2).getCellType() == 0) {
						dz = new Double(hssfrow.getCell((short) 2)
								.getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						dz = hssfrow.getCell((short) 2).getStringCellValue()
								.trim();
					}					
					if (hssfrow.getCell((short) 3) == null) {
						sslb = "";
					} else if (hssfrow.getCell((short) 3).getCellType() == 0) {
						sslb = new Double(hssfrow.getCell((short) 3)
								.getNumericCellValue()).toString();
					} else {// 如果EXCEL表格中的数据类型为字符串型
						sslb = hssfrow.getCell((short) 3).getStringCellValue()
								.trim();
					}					
					if(StringUtils.isEmpty(xh)){
						break;
					}else{
					
						YwRqQyjyxkRqssylb ywRqQyjyxkRqssylb = new YwRqQyjyxkRqssylb();
						ywRqQyjyxkRqssylb.setQyid(qyid);
						ywRqQyjyxkRqssylb.setAcceptNo(acceptNo);
						ywRqQyjyxkRqssylb.setUserName(username);
						ywRqQyjyxkRqssylb.setUploadDate(new Date());
						ywRqQyjyxkRqssylb.setXh(xh);
						ywRqQyjyxkRqssylb.setMc(mc);
						ywRqQyjyxkRqssylb.setDz(dz);	
						ywRqQyjyxkRqssylb.setSslb(sslb);
						//this.save(ywRqQyjyxkRqssylb);
						
						YwRqRqssylb ywRqRqssylb=new YwRqRqssylb();
						ywRqRqssylb.setSsmc(mc);
						ywRqRqssylb.setSsaddress(dz);
						ywRqRqssylb.setSslb(sslb);
						ywRqRqssylb.setQyjyxkid(rqjyxkid);
						if(StringUtils.isNotBlank(dz)){//地址不能为空
							ywRqRqssylbDao.save(ywRqRqssylb);
						}
					}					
				}				
			}
			ywRqRqssylbDao.deleteAll(qyid);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<YwRqQyjyxkRqssylb> getssList(String acceptNo,Long qyid)
	{
		//List<YwRqQyjyxkRqssylb> list=()ywRqQyjyxkRqssylbDao.createQuery("from YwRqQyjyxkRqssylb ssyl where ssyl.acceptNo='"+acceptNo+"' and ssyl.qyid="+qyid+""); 
		String sql ="from YwRqQyjyxkRqssylb ssyl where ssyl.acceptNo='"+acceptNo+"' and ssyl.qyid="+qyid+"";
		List<YwRqQyjyxkRqssylb> list=ywRqQyjyxkRqssylbDao.find(sql);
		return list;
	}

	/**
     * 导出 燃气设施一览表
     * 
     * @param hssfsheet
     * @param acceptNo
     * @param qyid
     * @param username
     * @return
     */
    public HSSFSheet expRqqxsbylb(HSSFSheet hssfsheet,
            String acceptNo, Long qyid, String username,HSSFCellStyle cs,Long ajid,String exptype){
    	if("qyxx".equals(exptype)){
    		 List<YwRqQyjyxkFinalRqssylb> entityList = this.rqssylbService.getInfosByQyid(qyid);
    	        if(entityList == null || entityList.size() <= 0) return null;
    	        if(entityList!=null){
    	          try {
    	            /**
    	             * i代表生成的列表行，j表示数据的下标
    	             */
    	            int xh=0;
    	            for(int i = 15, j = 0; j< entityList.size(); i++){
    	                if(i < 15) continue;
    	                xh++;
    	                YwRqQyjyxkFinalRqssylb entity = entityList.get(j);
    	                if(entity != null){
    	                    HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
    	                    HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text0 = new HSSFRichTextString(xh+"");// 声明文本内容
    	                    cell0.setCellValue(text0);// 将文本对象加入单元格
    	                    cell0.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text1 = new HSSFRichTextString(entity.getMc()==null?"":entity.getMc());// 声明文本内容
    	                    cell1.setCellValue(text1);// 将文本对象加入单元格
    	                    cell1.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell2 = row.createCell(2);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text2 = new HSSFRichTextString(entity.getDz()==null?"":entity.getDz());// 声明文本内容
    	                    cell2.setCellValue(text2);// 将文本对象加入单元格
    	                    cell2.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text3 = new HSSFRichTextString(entity.getSslb()==null?"":entity.getSslb());// 声明文本内容
    	                    cell3.setCellValue(text3);// 将文本对象加入单元格
    	                    cell3.setCellStyle(cs); 
    	                }
    	                j++;
    	            }
    	        } catch (Exception e) {
    	            e.printStackTrace();
    	        }
    	        }
    	}else{
    		 List<YwRqRqssylb> entityList = getListByQyId(ajid);
    	        if(entityList == null || entityList.size() <= 0) return null;
    	        if(entityList!=null)
    	        {
    	        try {
    	            /**
    	             * i代表生成的列表行，j表示数据的下标
    	             */
    	            int xh=0;
    	            for(int i = 15, j = 0; j< entityList.size(); i++){
    	                if(i < 15) continue;
    	                xh++;
    	                YwRqRqssylb entity = entityList.get(j);
    	                if(entity != null){
    	                    HSSFRow row = hssfsheet.createRow(i);// 创建一个行对象
    	                    HSSFCell cell0 = row.createCell(0);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text0 = new HSSFRichTextString(xh+"");// 声明文本内容
    	                    cell0.setCellValue(text0);// 将文本对象加入单元格
    	                    cell0.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell1 = row.createCell(1);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text1 = new HSSFRichTextString(entity.getSsmc()==null?"":entity.getSsmc());// 声明文本内容
    	                    cell1.setCellValue(text1);// 将文本对象加入单元格
    	                    cell1.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell2 = row.createCell(2);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text2 = new HSSFRichTextString(entity.getSsaddress()==null?"":entity.getSsaddress());// 声明文本内容
    	                    cell2.setCellValue(text2);// 将文本对象加入单元格
    	                    cell2.setCellStyle(cs); 
    	                    
    	                    HSSFCell cell3 = row.createCell(3);// 在本行的第一个单元格中写入数据
    	                    HSSFRichTextString text3 = new HSSFRichTextString(entity.getSslb()==null?"":entity.getSslb());// 声明文本内容
    	                    cell3.setCellValue(text3);// 将文本对象加入单元格
    	                    cell3.setCellStyle(cs); 
    	                }
    	                j++;
    	            }
    	        } catch (Exception e) {
    	            e.printStackTrace();
    	        }
    	        }
    	}
    	
       
        return hssfsheet;
    }
    
    /**
     * 查询设施一览表
     * @param ajid
     * @return
     */
    private List<YwRqRqssylb> getListByQyId(Long ajid){
        String hql = "from YwRqRqssylb ssyl where ssyl.qyjyxkid='" + ajid+"'";
        List<YwRqRqssylb> entityList = ywRqRqssylbDao.find(hql);
        return entityList;
    }

    public Map saveFinalEntity(YwRqQyjyxkFinalRqssylb finalentity) {
        rqssylbService.saveEntity(finalentity);
        return null;
    }

    public List<YwRqRqssylb> getssList(Long ajid) {
        return this.getListByQyId(ajid);
    }
    
    /*
     * 生成设施一览表的考核文件
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssylbService#buildFilePathByContent(java.lang.String, com.augurit.gzcgwsp.ssylb.entity.YwRqRqssylb)
     */
    public String createFilePathByContent(String contextPath,String sqdw, YwRqRqssylb entity) {
        String filename=this.getFileNameBySsLx(entity);
        log.info("生成的考核文件名称------------->"+filename);
        if(StringUtils.isNotBlank(filename)){
            String inFilePath=contextPath+"/Templates/rqss/"+filename;
            inFilePath=inFilePath.replace("/", File.separator);
            String tempOutPath="Templates/rqjyxk/ssylb/outfile";
            tempOutPath=this.buildPath(tempOutPath, "xls");
            String outfilepath=contextPath+"/"+tempOutPath;
            outfilepath=outfilepath.replace("/", File.separator);
            String outdir=outfilepath.substring(0,outfilepath.lastIndexOf(File.separator));
            File file=new File(outdir);
            if(!file.exists()){
                file.mkdirs();
            }
          try {
        	  //FileUtil.delDir(outdir, filename);
        	    //FileUtil.copyFile(inFilePath, outfilepath);
//        	    Workbook wb=ExcelUtil.getWorkBookByInputStream(new FileInputStream(outfilepath));
//        		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(outfilepath));// 将is流实例到
//        		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);// 第一个工作表
        	  // cell1.setCellStyle(cs); 
                writerDataToExcel(inFilePath, outfilepath,sqdw,entity);
				//上传附件到另外一台服务器 
				SFTPUtil.uploadOtherFile("/" + tempOutPath, outfilepath); 

            } catch (Exception e) {
             log.error("生成文件错误"+e);
            }
           return tempOutPath;
        }
      return null;
    }
    
    /**
     * 生成后面的文件路径加上年月
     * @param realpath 定义的目录
     * @param fileNamesuffix 文件后缀名
     * @return
     */
    private String buildPath(String realpath,String fileNamesuffix){
        String fileName = GetFileName.getFileName(fileNamesuffix);
        String dateDir = GetFileName.getDirector();
        return realpath+"/"+dateDir+"/"+fileName;
        
    }

    
    
    /**
     * 写入日期名称信息
     * @param inFilePath
     * @param outFilePath
     * @param entity
     * @return
     */
    private String writerDataToExcel(String inFilePath,String outFilePath,String sqdw,YwRqRqssylb entity){
        try {
      	  PoiExcelSheetCopy.copyFile(inFilePath, outFilePath);
    	  HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(outFilePath));
    	  HSSFSheet hssfsheet = workbook.getSheetAt(0);// 第一个工作表
    	  copyData(hssfsheet,sqdw,entity);
		  FileOutputStream fos = new FileOutputStream(outFilePath);
	      workbook.write(fos);
          fos.close();
        } catch (Exception e) {
           log.error("创建文件错误"+e);
        } 
        return outFilePath;
    }
    
    /**
     * 写入数据
     * @param ws
     * @param entity
     */
    private void copyData(HSSFSheet hssfsheet,String sqdw,YwRqRqssylb entity){
       try {
    	   HSSFRow hssfrow=null;
    	   if("加气站".equals(entity.getSslb()) || "汽车加气站".equals(entity.getSslb())
    			   ||"门站".equals(entity.getSslb())){//加气站写下一行
    		    hssfrow = hssfsheet.getRow(2);// 第一行
    	   }else{
    		    hssfrow = hssfsheet.getRow(1);// 第一行
           }
    	   
    	   HSSFCell cell1=hssfrow.createCell((short)0);   //创建一个单元格  HSSFCell类表示单元格  注册地址
    	   HSSFCell cell2=hssfrow.createCell((short)1); 
    	   //cell1.setCellValue(entity.getSsmc());   //设置单元格内容 
           if(entity!=null){
               String sskh=entity.getSskh();
               String dwmc="";
               if("1".equals(sskh) || "2".equals(sskh) || "3".equals(sskh)){
                   sqdw=entity.getSsmc()==null?"":entity.getSsmc();//供应站名称
                   dwmc="供应站名称："+sqdw;
               }else{
                   dwmc="企业名称："+sqdw;
               }
               Date nowdate =new Date();
               String date=DateUtilsEX.dateFormate(nowdate, "yyyy年MM月dd日");
               String content=dwmc+"        考核日期："+date;
               if("11".equals(sskh) || "12".equals(sskh) ){
            	   //管道里面的人工煤气储配站和lng气化站
            	   cell2.setCellValue(content);
               }else {
            	   cell1.setCellValue(content);
               }
               
           }
       }catch (Exception e) {
               log.error("受理编号"+"写入工作表信息错误"+e);
       }
    }
    
    /**
     * 根据设施类型判断调用的文件
     * @param entity
     * @return
     */
    private String getFileNameBySsLx(YwRqRqssylb entity){
        String dir="";
        String sslb=entity.getSslb();
        if("储配站".equals(sslb) || "灌装站".equals(sslb)){
            dir="chuguanzhan";
        }else if("加气站".equals(sslb)){
            dir="jiaqizhan";
        }else if("供应站".equals(sslb)){
            dir="gongyinzhan";
        }else if("供应站".equals(sslb)){
            dir="gongyinzhan";
        }else if("汽车加气站".equals(sslb)){
            dir="jiaqizhan";
        }else if("门站".equals(sslb)){
            dir="menzhan";
        }else if("门站".equals(sslb)){
            dir="menzhan";
        }else if("气化站".equals(sslb)){
            dir="qihuazhan";
        }else if("LNG气化站".equals(sslb) || "人工煤气储配站".equals(sslb)){
            dir="guandaoqishesi";
        }
        String filename="onelevelsupply.xls";
        if(StringUtils.isNotBlank(entity.getSskh())){
	        if("1".equals(entity.getSskh())){
	            filename="onelevelsupply.xls";//一级供应站许可登记考核表
	        }else if("2".equals(entity.getSskh())){
	            filename="twolevelsupply.xls";//二级供应站许可登记考核表
	        }else if("3".equals(entity.getSskh())){
	            filename="threelevelsupply.xls";//三级供应站许可登记考核表
	        }else if("4".equals(entity.getSskh())){
	            filename="pzaqInfo.xls";//液化气企业安全管理情况考核表
	        }else if("5".equals(entity.getSskh())){
	            if(StringUtils.isNotBlank(entity.getTotalVol()) && StringUtils.isNotBlank(entity.getSingleVol())){
	                //液化气储配站许可登记考核表
	                String totalVol=StringUtilsEX.getNumFromStr(entity.getTotalVol());
	                String singleVol=entity.getSingleVol();
	                filename="pzcpzxkInfo1.xls";
	                if(Integer.valueOf(totalVol)<=50 ){
	                    filename="pzcpzxkInfo1.xls";
	                }else if((Integer.valueOf(totalVol)>50 && Integer.valueOf(totalVol)<=200) ){
	                    filename="pzcpzxkInfo2.xls";
	                }else if((Integer.valueOf(totalVol)>200 && Integer.valueOf(totalVol)<=500) ){
	                    filename="pzcpzxkInfo3.xls";
	                }else if((Integer.valueOf(totalVol)>500 && Integer.valueOf(totalVol)<=1000) ){
	                    filename="pzcpzxkInfo4.xls";
	                }else if((Integer.valueOf(totalVol)>1000 && Integer.valueOf(totalVol)<=2500) ){
	                    filename="pzcpzxkInfo5.xls";
	                }else if((Integer.valueOf(totalVol)>2500 && Integer.valueOf(totalVol)<=5000) ){
	                    filename="pzcpzxkInfo6.xls";
	                }else if((Integer.valueOf(totalVol)>5000)){
	                    filename="pzcpzxkInfo7.xls";
	                }else{
	                	filename="onelevelsupply.xls";
	                }
	            }
	        }else if("6".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面二级)
	            filename="jqzxkdmtwolevel.xls";
	        }else if("7".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面三级)
	            filename="jqzxkdmthreelevel.xls";
	        }else if("8".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面二级)
	            filename="jqzxkmdtwolevel.xls";
	        }else if("9".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面三级)
	            filename="jqzxkmdthreelevel.xls";
	        }else if("10".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面三级)
	            filename="kaohemenzhan.xls";
	        }
	        else if("11".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面三级)
	            filename="LNGqihuazhan.xls";
	        }
	        else if("12".equals(entity.getSskh())){
	            //汽车加气站许可登记考核表(地面三级)
	            filename="rengongmeiqichupeizhan.xls";
	        }else if("15".equals(entity.getSskh())){
	            //液化气气化站考核表
	            filename="qizhuazhankaohe.xls";
	        }
       }else{
    	   if("储配站".equals(sslb) || "灌装站".equals(sslb)){
    		   filename="pzcpzxkInfo3.xls";
           }else if("加气站".equals(sslb)){
        	   filename="jqzxkdmthreelevel.xls";
           }else if("汽车加气站".equals(sslb)){
        	   filename="jqzxkdmthreelevel.xls";
           }
       }
        if(StringUtils.isBlank(dir)){
            return "pzaqInfo.xls";
        }
        return dir+File.separator+filename;
    }

    /*
     * 根据业务id和名称查找设施一览表
     * @see com.augurit.gzcgwsp.excelimportexport.service.IYwRqQyjyxkRqssylbService#getSsylbByName(java.lang.Long, java.lang.String)
     */	
	public List<YwRqRqssylb> getSsylbByName(Long rqjyxkid, String ssmc) {
			String sql = "from YwRqRqssylb  t where t.qyjyxkid = "+rqjyxkid+" and t.ssmc='"+ssmc+"'";
			return this.ywRqRqssylbDao.find(sql);
	}

	public void saveEntity(YwRqRqssylb entity) {
		ywRqRqssylbDao.save(entity);
		
	}
    
}
